﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using FrankJie.IDAL;
using FrankJie.Model;

namespace FrankJie.SQLServerDAL
{
	/// <summary>
	/// 文章数据访问类
	/// </summary>
	public partial class Post:IPost
	{
        /// <summary>
        /// 增加一篇文章
        /// </summary>
        /// <param name="postInfo">文章实体</param>
        /// <returns>返回文章ID</returns>
        public int Add(PostInfo postInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO tblPost(");
            strSql.Append("Title,Excerpt,Contents,Tag,Status,Password,CreateTime,Guid,Comment,,MId)");
            strSql.Append(" VALUES (");
            strSql.Append("@Title,@Excerpt,@Contents,@Tag,@Status,@Password,@CreateTime,@Guid,@Comment,@MId)");
            strSql.Append(";SELECT @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Title", SqlDbType.VarChar,50),
					new SqlParameter("@Excerpt", SqlDbType.VarChar,200),
					new SqlParameter("@Contents", SqlDbType.VarChar),
					new SqlParameter("@Tag", SqlDbType.VarChar,200),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@Password", SqlDbType.VarChar,50),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@Guid", SqlDbType.VarChar,50),
					new SqlParameter("@Comment", SqlDbType.Bit,1),
					new SqlParameter("@MId", SqlDbType.Int,4)};
            parameters[0].Value = postInfo.Title;
            parameters[1].Value = postInfo.Excerpt;
            parameters[2].Value = postInfo.Contents;
            parameters[3].Value = postInfo.Tag;
            parameters[4].Value = postInfo.Status;
            parameters[5].Value = postInfo.Password;
            parameters[6].Value = postInfo.CreateTime;
            parameters[7].Value = postInfo.Guid;
            parameters[8].Value = postInfo.Comment;
            parameters[9].Value = postInfo.MId;

            object obj = SQLHelper.GetSingle(SQLHelper.FrankJieDBConnStr,CommandType.Text,strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一篇文章
        /// </summary>
        /// <param name="postInfo">文章实体</param>
        /// <returns>返回是否成功</returns>
        public bool Update(PostInfo postInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE tblPost SET ");
            strSql.Append("Title=@Title,Excerpt=@Excerpt,Contents=@Contents,Tag=@Tag,Status=@Status,Password=@Password,UpdateTime=@UpdateTime,MId=@MId");
            strSql.Append(" WHERE Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Title", SqlDbType.VarChar,50),
					new SqlParameter("@Excerpt", SqlDbType.VarChar,200),
					new SqlParameter("@Contents", SqlDbType.VarChar),
					new SqlParameter("@Tag", SqlDbType.VarChar,200),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@Password", SqlDbType.VarChar,50),
					new SqlParameter("@UpdateTime", SqlDbType.DateTime),
					new SqlParameter("@Guid", SqlDbType.VarChar,50),
					new SqlParameter("@Comment", SqlDbType.Bit,1),
					new SqlParameter("@MId", SqlDbType.Int,4),
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = postInfo.Title;
            parameters[1].Value = postInfo.Excerpt;
            parameters[2].Value = postInfo.Contents;
            parameters[3].Value = postInfo.Tag;
            parameters[4].Value = postInfo.Status;
            parameters[5].Value = postInfo.Password;
            parameters[6].Value = postInfo.UpdateTime;
            parameters[7].Value = postInfo.Guid;
            parameters[8].Value = postInfo.Comment;
            parameters[9].Value = postInfo.MId;
            parameters[10].Value = postInfo.Id;

            int rows = SQLHelper.ExecuteSql(SQLHelper.FrankJieDBConnStr,CommandType.Text,strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更改文章状态
        /// </summary>
        /// <param name="id">文章id</param>
        /// <param name="Status">状态 PostStatus枚举</param>
        /// <returns>返回是否成功</returns>
        public bool UpdateStatus(int id, int status)
        {
            string strSql = "UPDATE tblPost SET Status=@Status WHERE Id=@Id";
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4),
                    new SqlParameter("@Status", SqlDbType.Int,4)};
            parameters[0].Value = id;
            parameters[1].Value = status;
            int rows = SQLHelper.ExecuteSql(SQLHelper.FrankJieDBConnStr, CommandType.Text, strSql, parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
		/// 删除一篇文章
		/// </summary>
        /// <param name="id">文章id</param>
        /// <returns>返回是否成功</returns>
		public bool Delete(int id)
        {
            string strSql = "DELETE FROM tblPost WHERE Id=@Id";
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            int rows = SQLHelper.ExecuteSql(SQLHelper.FrankJieDBConnStr, CommandType.Text, strSql, parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据文章id获取文章
        /// </summary>
        /// <param name="id">文章id</param>
        /// <returns>返回文章实体</returns>
        public PostInfo GetPostById(int id)
        {
            string strSql = "SELECT * FROM tblPost WHERE Id=@Id";
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            PostInfo postInfo = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.FrankJieDBConnStr, CommandType.Text, strSql, parameters))
            {
                if (!dr.HasRows)
                {
                    return null;
                }
                postInfo = new PostInfo();
                postInfo.Id = int.Parse(dr["Id"].ToString());
                postInfo.Title = dr["Title"].ToString();
                postInfo.Excerpt = dr["Excerpt"].ToString();
                postInfo.Contents = dr["Contents"].ToString();
                postInfo.Tag = dr["Tag"].ToString();
                postInfo.Status = int.Parse(dr["Status"].ToString());
                postInfo.Password = dr["Password"].ToString();
                postInfo.CreateTime = DateTime.Parse(dr["CreateTime"].ToString());
                postInfo.UpdateTime = DateTime.Parse(dr["UpdateTime"].ToString());
                postInfo.Guid = dr["Guid"].ToString();
                if ((dr["Comment"].ToString() == "1") || (dr["Comment"].ToString().ToLower() == "true"))
                {
                    postInfo.Comment = true;
                }
                else
                {
                    postInfo.Comment = false;
                }
                postInfo.Commentcount = int.Parse(dr["Commentcount"].ToString());
                postInfo.MId = int.Parse(dr["MId"].ToString());
            }
            return postInfo;
        }

        /// <summary>
        /// 获取文章列表
        /// </summary>
        /// <returns>返回文章集合</returns>
        public List<PostInfo> GetList()
        {
            string strSql = "SELECT * FROM tblPost";
            List<PostInfo> postInfoList = new List<PostInfo>();
            PostInfo postInfo = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.FrankJieDBConnStr, CommandType.Text, strSql))
            {
                postInfo = new PostInfo();
                postInfo.Id = int.Parse(dr["Id"].ToString());
                postInfo.Title = dr["Title"].ToString();
                postInfo.Excerpt = dr["Excerpt"].ToString();
                postInfo.Contents = dr["Contents"].ToString();
                postInfo.Tag = dr["Tag"].ToString();
                postInfo.Status = int.Parse(dr["Status"].ToString());
                postInfo.Password = dr["Password"].ToString();
                postInfo.CreateTime = DateTime.Parse(dr["CreateTime"].ToString());
                postInfo.UpdateTime = DateTime.Parse(dr["UpdateTime"].ToString());
                postInfo.Guid = dr["Guid"].ToString();
                if ((dr["Comment"].ToString() == "1") || (dr["Comment"].ToString().ToLower() == "true"))
                {
                    postInfo.Comment = true;
                }
                else
                {
                    postInfo.Comment = false;
                }
                postInfo.Commentcount = int.Parse(dr["Commentcount"].ToString());
                postInfo.MId = int.Parse(dr["MId"].ToString());
                postInfoList.Add(postInfo);
            }
            return postInfoList;
        }

        /// <summary>
        /// 增加浏览数
        /// </summary>
        /// <param name="dic"></param>
        public void AddViewCount(Dictionary<int, int> dic)
        {
            StringBuilder strSql = new StringBuilder();
            foreach (KeyValuePair<int, int> keyValue in dic)
            {
                strSql.AppendFormat("UPDATE tblPost SET ViewCount=ViewCount+{1} WHERE Id={0};", keyValue.Key, keyValue.Value);
            }
            SQLHelper.ExecuteSql(SQLHelper.FrankJieDBConnStr, CommandType.Text, strSql.ToString());
        }
	}
}

